import os
import json
import pandas as pd
import re
from scrawler import get_all_schools, sanitize_filename, get_raw_data_path

def load_raw_data(file_path):
    """从本地文件加载原始数据"""
    try:
        with open(file_path, 'r', encoding='utf-8') as f:
            return json.load(f)
    except (FileNotFoundError, json.JSONDecodeError):
        return None

def determine_subject_type(item):
    """根据type字段判断文理科类型"""
    type_value = item.get('type')
    
    if type_value in ['1', '2073']:  # 理科/物理类
        return 'science'
    elif type_value in ['2', '2074']:  # 文科/历史类
        return 'art'
    elif type_value in ['5', '24', '2294', '2295']:  # 体育类
        return 'other_sport'
    elif type_value in ['4', '25', '26', '2292', '2293']:  # 艺术类
        return 'other_art'
    else:
        print(f"未知科目类型: {type_value}，请检查数据格式")
        exit(1)
        return 'unknown'

def parse_all_data():
    """解析所有已爬取的原始数据并生成CSV"""
    output_path = './data/professional_score.csv'
    
    # 确保输出目录存在
    os.makedirs(os.path.dirname(output_path), exist_ok=True)

    schools = get_all_schools()
    if not schools:
        print("Failed to get school list")
        return

    all_records = []
    total_schools = len(schools)
    processed_schools = 0
    
    for school_id, school_info in schools.items():
        processed_schools += 1
        school_name = school_info.get('name', 'Unknown')
        print(f"解析学校 {processed_schools}/{total_schools}: {school_name} (ID: {school_id})")
        
        school_total_records = 0
        for year in range(2018, 2025):
            raw_file_path = get_raw_data_path(school_id, year, school_name)
            
            if not os.path.exists(raw_file_path):
                continue
                
            raw_data = load_raw_data(raw_file_path)
            if not raw_data or raw_data.get('code') != '0000':
                continue
                
            special_scores = raw_data.get('data')
            if special_scores:
                year_count = 0
                for _, special_data in special_scores.items():
                    for item in special_data.get('item', []):
                        all_records.append({
                            'school_id': item.get('school_id'),
                            'school_name': school_name,
                            'year': year,
                            'spname': item.get('sp_name') or item.get('spname'),
                            'spname_full': item.get('spname') or item.get('sp_name'),
                            'sg_name': item.get('sg_name'),
                            'zs_type': item.get('zslx_name'),
                            'local_batch_name': item.get('local_batch_name'),
                            'subject': determine_subject_type(item),
                            'min': item.get('min'),
                            'max': item.get('max'),
                            'average': item.get('average'),
                            'min_section': item.get('min_section'),
                        })
                        year_count += 1
                        school_total_records += 1
                print(f"  {year} 年: {year_count} 条记录")
        
        print(f"  学校总计: {school_total_records} 条记录")

    # 保存所有数据到CSV和Excel
    if all_records:
        df = pd.DataFrame(all_records)
        df.to_csv(output_path, index=False)
        
        # 同时保存为Excel格式
        excel_output_path = './data/professional_score.xlsx'
        df.to_excel(excel_output_path, index=False)
        
        print(f"\n解析完成！已保存 {len(all_records)} 条记录到:")
        print(f"  CSV: {output_path}")
        print(f"  Excel: {excel_output_path}")
        
        # 显示subject字段的统计信息
        subject_counts = df['subject'].value_counts()
        print(f"\n科目类型统计:")
        for subject, count in subject_counts.items():
            print(f"  {subject}: {count} 条记录")
    else:
        print("\n没有找到可解析的数据")

def main():
    parse_all_data()

if __name__ == '__main__':
    main()
